import frappe
from frappe import _
from frappe.utils import flt, getdate, add_months, formatdate, relativedelta
from erpnext.accounts.report.financial_statements import (
    set_gl_entries_by_account
)

value_fields = (
    "opening_debit", "opening_credit",
    "debit", "credit",
    "closing_debit", "closing_credit"
)

def execute(filters=None):
    filters = frappe._dict(filters or {})
    validate_filters(filters)
    add_period_dates(filters)
    columns = get_columns(filters)
    data = get_data(filters)
    return columns, data

def validate_filters(filters):
    """验证筛选条件的有效性"""
    if not filters.company:
        frappe.throw(_("公司为必填项"))
    if not filters.fiscal_year:
        frappe.throw(_("会计年度为必填项"))
    
    # 获取会计年度起止日期
    fiscal_year = frappe.get_cached_value(
        "Fiscal Year", filters.fiscal_year, 
        ["year_start_date", "year_end_date"], as_dict=True
    )
    if not fiscal_year:
        frappe.throw(_("会计年度 {0} 不存在").format(filters.fiscal_year))
    
    filters.year_start_date = getdate(fiscal_year.year_start_date)
    filters.year_end_date = getdate(fiscal_year.year_end_date)
    
    # 处理日期筛选（优先使用from_date/to_date）
    if filters.get("from_date"):
        filters.period_start_date = getdate(filters.from_date)
    if filters.get("to_date"):
        filters.period_end_date = getdate(filters.to_date)

def add_period_dates(filters):
    """计算期间起止日期（优先使用from_date/to_date，否则使用会计年度）"""
    if hasattr(filters, 'period_start_date') and hasattr(filters, 'period_end_date'):
        return
    
    # 未指定日期时使用会计年度完整日期
    filters.period_start_date = filters.year_start_date
    filters.period_end_date = filters.year_end_date

def get_columns(filters):
    """定义报表列结构"""
    company_currency = frappe.get_cached_value("Company", filters.company, "default_currency")
    columns = [
        {
            "fieldname": "account",
            "label": _("会计科目"),
            "fieldtype": "Link",
            "options": "Account",
            "width": 300
        },
        {
            "fieldname": "account_name",
            "label": _("科目名称"),
            "fieldtype": "Data",
            "width": 200
        },
        {
            "fieldname": "amount_type",
            "label": _("金额类型"),
            "fieldtype": "Data",
            "width": 120
        }
    ]
    
    # 获取期间内的所有月份
    months = get_months_in_period(filters.period_start_date, filters.period_end_date)
    
    # 如果需要显示月度趋势，添加月度列
    if filters.trends:
        for month_start, month_end in months:
            month_label = formatdate(month_start, "MMM yyyy")
            columns.append({
                "fieldname": f"amount_{month_start.strftime('%Y%m')}",
                "label": month_label,
                "fieldtype": "Currency",
                "options": company_currency,
                "width": 140
            })
        
        # 添加总计列
        columns.append({
            "fieldname": "total",
            "label": _("总计"),
            "fieldtype": "Currency",
            "options": company_currency,
            "width": 140
        })
    else:
        # 标准列
        columns.extend([
            {
                "fieldname": "opening_debit",
                "label": _("期初借方"),
                "fieldtype": "Currency",
                "options": company_currency,
                "width": 120
            },
            {
                "fieldname": "opening_credit",
                "label": _("期初贷方"),
                "fieldtype": "Currency",
                "options": company_currency,
                "width": 120
            },
            {
                "fieldname": "debit",
                "label": _("本期借方"),
                "fieldtype": "Currency",
                "options": company_currency,
                "width": 120
            },
            {
                "fieldname": "credit",
                "label": _("本期贷方"),
                "fieldtype": "Currency",
                "options": company_currency,
                "width": 120
            },
            {
                "fieldname": "closing_debit",
                "label": _("期末借方"),
                "fieldtype": "Currency",
                "options": company_currency,
                "width": 120
            },
            {
                "fieldname": "closing_credit",
                "label": _("期末贷方"),
                "fieldtype": "Currency",
                "options": company_currency,
                "width": 120
            }
        ])
    
    return columns

def get_months_in_period(start_date, end_date):
    """获取期间内的所有月份"""
    months = []
    current_date = start_date
    while current_date <= end_date:
        # 获取当月最后一天
        next_month = add_months(current_date, 1)
        month_end = getdate(next_month) - relativedelta(days=1)
        if month_end > end_date:
            month_end = end_date
        months.append((current_date, month_end))
        current_date = next_month
    return months

def get_data(filters):
    """获取并处理报表数据"""
    accounts = get_accounts(filters)
    if not accounts:
        return []
    
    # 计算科目层级（indent）
    accounts = calculate_account_indent(accounts)
    
    # 构建科目映射关系
    accounts_by_name = {acc.name: acc for acc in accounts}
    parent_children_map = {}
    for acc in accounts:
        parent = acc.parent_account or ""
        parent_children_map.setdefault(parent, []).append(acc.name)
    
    # 获取期间内的所有月份
    months = get_months_in_period(filters.period_start_date, filters.period_end_date)
    month_keys = [month_start.strftime('%Y%m') for month_start, _ in months]
    
    # 初始化月度总账数据存储字典
    monthly_gl_entries = {}
    if filters.trends:
        for month_start, month_end in months:
            month_key = month_start.strftime('%Y%m')
            monthly_gl_entries[month_key] = {}
            set_gl_entries_by_account(
                filters.company,
                month_start,
                month_end,
                filters,
                monthly_gl_entries[month_key],
                ignore_closing_entries=True
            )
    else:
        # 获取总账分录数据
        gl_entries_by_account = {}
        # 确保传递正确的日期参数结构
        date_filters = {
            "period_start_date": filters.period_start_date,
            "period_end_date": filters.period_end_date
        }
        set_gl_entries_by_account(
            filters.company,
            filters.period_start_date,
            filters.period_end_date,
            date_filters,  # 传递包含日期的字典
            gl_entries_by_account,
            ignore_closing_entries=True
        )
    
    # 计算期初余额
    opening_balances = get_opening_balances(filters, accounts_by_name)
    
    # 计算每个科目的余额
    if filters.trends:
        calculate_monthly_values(accounts, monthly_gl_entries, month_keys, opening_balances)
    else:
        calculate_values(accounts, gl_entries_by_account, opening_balances)
    
    # 汇总子科目到父科目
    accumulate_values_into_parents(accounts, accounts_by_name, filters, month_keys)
    
    # 准备报表展示数据
    data = prepare_data(accounts, parent_children_map, filters, month_keys)
    
    # 过滤零值行（根据用户选择）
    show_zero = filters.get("show_zero_values", 0)
    if not show_zero:
        data = filter_out_zero_value_rows(data, filters)
    
    return data

def filter_out_zero_value_rows(data, filters):
    """过滤零值行"""
    filtered_data = []
    for row in data:
        if filters.trends:
            # 检查月度趋势行是否有值
            has_value = any(flt(row.get(f"amount_{month_key}")) != 0 for month_key in row.get("month_keys", [])) or flt(row.get("total")) != 0
        else:
            # 检查标准行是否有值
            has_value = any([
                flt(row["opening_debit"]) != 0,
                flt(row["opening_credit"]) != 0,
                flt(row["debit"]) != 0,
                flt(row["credit"]) != 0,
                flt(row["closing_debit"]) != 0,
                flt(row["closing_credit"]) != 0
            ])
        
        if has_value:
            filtered_data.append(row)
    
    return filtered_data

def get_accounts(filters):
    """获取指定公司/父科目的所有科目（含多级子科目）"""
    accounts = frappe.db.sql("""
        select name, account_number, parent_account, account_name, is_group, lft, rgt
        from `tabAccount` 
        where company = %s 
        order by lft
    """, filters.company, as_dict=True)
    
    if filters.get("account"):
        try:
            parent = frappe.get_doc("Account", filters.account)
            
            # 双重验证：lft/rgt范围 + 递归子科目
            accounts_by_lft_rgt = [a for a in accounts if a.lft >= parent.lft and a.rgt <= parent.rgt]
            child_accounts = [parent.name]
            
            def get_children(parent_name):
                children = [a.name for a in accounts if a.parent_account == parent_name]
                for child in children:
                    child_accounts.append(child)
                    get_children(child)
            
            get_children(parent.name)
            accounts = [a for a in accounts_by_lft_rgt if a.name in child_accounts]
            
        except Exception as e:
            frappe.log_error(f"筛选父科目失败: {str(e)}", "Disclosure Details CN")
            return []
    
    return accounts

def calculate_account_indent(accounts):
    """计算科目层级（indent）"""
    parent_children_map = {}
    account_map = {acc.name: acc for acc in accounts}
    
    # 构建父子关系映射
    for acc in accounts:
        parent = acc.parent_account or ""
        parent_children_map.setdefault(parent, []).append(acc.name)
    
    # 递归设置层级
    def set_indent(account_name, current_indent):
        acc = account_map.get(account_name)
        if acc:
            acc.indent = current_indent
            for child_name in parent_children_map.get(account_name, []):
                set_indent(child_name, current_indent + 1)
    
    # 从根科目开始
    root_accounts = parent_children_map.get("", [])
    for root in root_accounts:
        set_indent(root, 0)
    
    return accounts

def get_opening_balances(filters, accounts_by_name):
    """获取科目期初余额"""
    opening_balances = {
        name: frappe._dict({"opening_debit": 0.0, "opening_credit": 0.0})
        for name in accounts_by_name
    }
    
    # 计算会计年度内的期初余额
    if filters.period_start_date > filters.year_start_date:
        result = frappe.db.sql("""
            select account, sum(debit) as debit, sum(credit) as credit
            from `tabGL Entry`
            where company = %s
                and posting_date >= %s
                and posting_date < %s
                and is_cancelled = 0
            group by account
        """, (filters.company, filters.year_start_date, filters.period_start_date), as_dict=True)
        
        for d in result:
            if d.account in opening_balances:
                opening_balances[d.account].opening_debit = flt(d.debit)
                opening_balances[d.account].opening_credit = flt(d.credit)
    
    return opening_balances

def calculate_values(accounts, gl_entries_by_account, opening_balances):
    """计算每个科目的余额（非月度趋势模式）"""
    for acc in accounts:
        # 期初余额
        ob = opening_balances.get(acc.name, {})
        acc.opening_debit = flt(ob.get("opening_debit", 0.0))
        acc.opening_credit = flt(ob.get("opening_credit", 0.0))
        
        # 本期发生额
        acc.debit = 0.0
        acc.credit = 0.0
        for entry in gl_entries_by_account.get(acc.name, []):
            acc.debit += flt(entry.debit)
            acc.credit += flt(entry.credit)
        
        # 期末余额
        acc.closing_debit = acc.opening_debit + acc.debit
        acc.closing_credit = acc.opening_credit + acc.credit

def calculate_monthly_values(accounts, monthly_gl_entries, month_keys, opening_balances):
    """计算每个科目的月度余额（月度趋势模式）"""
    for acc in accounts:
        # 初始化月度借方和贷方金额
        for month_key in month_keys:
            setattr(acc, f"debit_{month_key}", 0.0)
            setattr(acc, f"credit_{month_key}", 0.0)
        
        # 计算每个月的发生额
        for month_key, gl_entries in monthly_gl_entries.items():
            for entry in gl_entries.get(acc.name, []):
                debit_field = f"debit_{month_key}"
                credit_field = f"credit_{month_key}"
                setattr(acc, debit_field, flt(getattr(acc, debit_field, 0.0)) + flt(entry.debit))
                setattr(acc, credit_field, flt(getattr(acc, credit_field, 0.0)) + flt(entry.credit))

def accumulate_values_into_parents(accounts, accounts_by_name, filters, month_keys):
    """汇总子科目到父科目"""
    # 按层级从深到浅处理，确保子科目先于父科目处理
    for acc in reversed(accounts):
        if acc.parent_account and acc.parent_account in accounts_by_name:
            parent = accounts_by_name[acc.parent_account]
            if filters.trends:
                # 汇总月度数据
                for month_key in month_keys:
                    parent_debit = f"debit_{month_key}"
                    parent_credit = f"credit_{month_key}"
                    setattr(parent, parent_debit, flt(getattr(parent, parent_debit, 0.0)) + flt(getattr(acc, parent_debit, 0.0)))
                    setattr(parent, parent_credit, flt(getattr(parent, parent_credit, 0.0)) + flt(getattr(acc, parent_credit, 0.0)))
            else:
                # 汇总标准数据
                for field in value_fields:
                    parent[field] += acc[field]

def prepare_data(accounts, parent_children_map, filters, month_keys):
    """准备报表展示数据"""
    data = []
    # 按层级顺序处理科目，确保父科目在子科目之前显示
    for acc in accounts:
        if filters.trends:
            # 为每个科目添加两行：一行借方，一行贷方
            # 借方行
            debit_row = {
                "account": acc.name,
                "account_name": acc.account_name,
                "parent_account": acc.parent_account,
                "indent": acc.indent,
                "is_group": acc.is_group,
                "amount_type": _("本期借方"),
                "month_keys": month_keys
            }
            
            # 贷方行
            credit_row = {
                "account": acc.name,
                "account_name": acc.account_name,
                "parent_account": acc.parent_account,
                "indent": acc.indent,
                "is_group": acc.is_group,
                "amount_type": _("本期贷方"),
                "month_keys": month_keys
            }
            
            # 填充月度数据并计算总计
            total_debit = 0.0
            total_credit = 0.0
            
            for month_key in month_keys:
                debit_amount = flt(getattr(acc, f"debit_{month_key}", 0.0), 2)
                credit_amount = flt(getattr(acc, f"credit_{month_key}", 0.0), 2)
                
                debit_row[f"amount_{month_key}"] = debit_amount
                credit_row[f"amount_{month_key}"] = credit_amount
                
                total_debit += debit_amount
                total_credit += credit_amount
            
            # 设置总计
            debit_row["total"] = total_debit
            credit_row["total"] = total_credit
            
            # 添加到数据列表
            data.append(debit_row)
            data.append(credit_row)
        else:
            # 处理标准数据
            row = {
                "account": acc.name,
                "account_name": acc.account_name,
                "parent_account": acc.parent_account,
                "indent": acc.indent,
                "is_group": acc.is_group,
                "opening_debit": flt(acc.opening_debit, 2),
                "opening_credit": flt(acc.opening_credit, 2),
                "debit": flt(acc.debit, 2),
                "credit": flt(acc.credit, 2),
                "closing_debit": flt(acc.closing_debit, 2),
                "closing_credit": flt(acc.closing_credit, 2)
            }
            data.append(row)
    
    return data